package com.demo.employees.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.demo.employees.dao.EmployeeDAO;
import com.demo.employees.model.Employee;
import com.demo.employees.util.DBUtils;

/**
 * DAO的JDBC实现类
 * @author: jie.deng
 * @time: 2017年4月15日 上午1:46:54
 */
public class EmployeeDAOImpl implements EmployeeDAO {
	
	@Override
	public void saveEmployee(Employee employee) {
		Connection conn = null ;
		PreparedStatement ps = null;
		try {
			//1.获取Connection对象
			conn = DBUtils.getConnection();
			
			//2.准备执行语句PreparedStatement
			String sql = "insert into EMPLOYEE(NAME,AGE,SALARY,HIREDATE) values(?, ?, ?, ?)";
			ps = conn.prepareStatement(sql);
			//3.入参
			ps.setString(1, employee.getName());
			ps.setInt(2, employee.getAge());
			ps.setBigDecimal(3, employee.getSalary());
			ps.setDate(4, new java.sql.Date(employee.getHireDate().getTime()));
			
			//4.执行SQL
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//5.关闭ResultSet-Statement-Connection
		DBUtils.closeConn(conn, ps, null);
	}

	@Override
	public void updateEmployee(Employee employee) {
		Connection conn = null ;
		PreparedStatement ps = null;
		try {
			//1.获取Connection对象
			conn = DBUtils.getConnection();
			
			//2.准备执行语句PreparedStatement
			String sql = "update EMPLOYEE e set e.NAME = ?, e.AGE = ?, e.SALARY = ?, e.HIREDATE = ? where e.ID = ?";
			ps = conn.prepareStatement(sql);
			
			//3.入参
			ps.setString(1, employee.getName());
			ps.setInt(2, employee.getAge());
			ps.setBigDecimal(3, employee.getSalary());
			ps.setDate(4, new java.sql.Date(employee.getHireDate().getTime()));
			ps.setLong(5, employee.getId());
			
			//4.执行SQL
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//5.关闭ResultSet-Statement-Connection
		DBUtils.closeConn(conn, ps, null);
	}

	@Override
	public Employee getById(Long id) {
		//待返回对象
		Employee employee = null;
		
		Connection conn = null ;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//1.获取Connection对象
			conn = DBUtils.getConnection();
			
			//2.准备执行语句PreparedStatement
			String sql = "select ID,NAME,AGE,SALARY,HIREDATE from EMPLOYEE e where e.ID=?";
			ps = conn.prepareStatement(sql);
			
			//3.入参
			ps.setLong(1, id);
			
			//4.执行查询，获取ResultSet对象
			rs = ps.executeQuery();
			
			//5.查询结果封装
			if (rs.next()) {
				employee = new Employee();
				employee.setId(rs.getLong("ID"));
				employee.setName(rs.getString("NAME"));
				employee.setAge(rs.getInt("AGE"));
				employee.setSalary(rs.getBigDecimal("SALARY"));
				employee.setHireDate(rs.getDate("HIREDATE"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//6.关闭ResultSet-Statement-Connection
		DBUtils.closeConn(conn, ps, rs);
		//返回结果
		return employee;
	}

	@Override
	public List<Employee> findAll() {
		//待返回对象
		List<Employee> employees = new ArrayList<>();
		
		Connection conn = null ;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//1.获取Connection对象
			conn = DBUtils.getConnection();
			
			//2.准备执行语句PreparedStatement
			String sql = "select ID,NAME,AGE,SALARY,HIREDATE from EMPLOYEE e";
			ps = conn.prepareStatement(sql);
			
			//3.入参
			
			//4.执行查询，获取ResultSet对象
			rs = ps.executeQuery();
			
			//5.查询结果封装
			while (rs.next()) {
				Employee employee = new Employee();
				employee.setId(rs.getLong("ID"));
				employee.setName(rs.getString("NAME"));
				employee.setAge(rs.getInt("AGE"));
				employee.setSalary(rs.getBigDecimal("SALARY"));
				employee.setHireDate(rs.getDate("HIREDATE"));
				employees.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//6.关闭ResultSet-Statement-Connection
		DBUtils.closeConn(conn, ps, rs);
		//返回结果
		return employees;
	}

	@Override
	public void deleteById(Long id) {
		Connection conn = null ;
		PreparedStatement ps = null;
		try {
			//1.获取Connection对象
			conn = DBUtils.getConnection();
			
			//2.准备执行语句PreparedStatement
			String sql = "delete from EMPLOYEE where ID=?";
			ps = conn.prepareStatement(sql);
			
			//3.入参
			ps.setLong(1, id);
			
			//4.执行SQL
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//5.关闭ResultSet-Statement-Connection
		DBUtils.closeConn(conn, ps, null);
	}

	@Override
	public int getCount() {
		Connection conn = null ;
		PreparedStatement ps = null;
		ResultSet rs = null;
		//待返回对象
		int count = 0;
		
		try {
			//1.获取Connection对象
			conn = DBUtils.getConnection();
			
			//2.准备执行语句PreparedStatement
			String sql = "select count(1) from EMPLOYEE e";
			ps = conn.prepareStatement(sql);
			
			//3.入参
			
			//4.执行查询，获取ResultSet对象
			rs = ps.executeQuery();
			
			//5.ResultSet--->结果
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//6.关闭ResultSet-Statement-Connection
		DBUtils.closeConn(conn, ps, rs);
		//返回结果
		return count;
	}

}
